package com.ibuscloud.union.mapper;

import com.ibuscloud.union.model.YearCount;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 一年的核销统计表 Mapper 接口
 * </p>
 *
 * @author 书瞳
 * @since 2022-12-08
 */
public interface YearCountMapper extends BaseMapper<YearCount> {

     List<YearCount> calcYearCount(@Param("dayTime") String dayTime);

     @Select("SELECT e.tickettypeid,#{dealDate} as date,\n" +
             "       e.tickettypename,\n" +
             "       pd_price,\n" +
             "       pd_count,\n" +
             "       rd_count,\n" +
             "       rd_price,\n" +
             "       one,\n" +
             "       one_half,\n" +
             "       two,\n" +
             "       two_half,\n" +
             "       three,\n" +
             "       over_three\n" +
             "FROM (SELECT a.tickettypeid,\n" +
             "             a.tickettypename,\n" +
             "             pd_price,\n" +
             "             pd_count,\n" +
             "             one,\n" +
             "             one_half,\n" +
             "             two,\n" +
             "             two_half,\n" +
             "             three,\n" +
             "             over_three\n" +
             "      FROM (SELECT tickettypeid,\n" +
             "                   tickettypename,\n" +
             "                   SUM(price) AS pd_price,\n" +
             "                   Count(tickettypeid)  AS pd_count\n" +
             "            FROM tb_pd\n" +
             "            GROUP BY tickettypeid,\n" +
             "                     tickettypename) a\n" +
             "               left join (SELECT tickettypeid,\n" +
             "                                 tickettypename,\n" +
             "                                 SUM(one)        AS one,\n" +
             "                                 SUM(one_half)   AS one_half,\n" +
             "                                 SUM(two)        AS two,\n" +
             "                                 SUM(two_half)   AS two_half,\n" +
             "                                 SUM(three)      AS three,\n" +
             "                                 SUM(over_three) AS over_three\n" +
             "                          FROM (SELECT tickettypeid,\n" +
             "                                       tickettypename,\n" +
             "                                       IF(transamount <= 100, 1, 0)         AS\n" +
             "                                           one,\n" +
             "                                       IF(transamount > 100\n" +
             "                                              AND transamount <= 150, 1, 0) AS\n" +
             "                                           one_half,\n" +
             "                                       IF(transamount > 150\n" +
             "                                              AND transamount <= 200, 1, 0) AS\n" +
             "                                           two,\n" +
             "                                       IF(transamount > 200\n" +
             "                                              AND transamount <= 250, 1, 0) AS\n" +
             "                                           two_half,\n" +
             "                                       IF(transamount > 250\n" +
             "                                              AND transamount <= 300, 1, 0) AS\n" +
             "                                           three,\n" +
             "                                       IF(transamount > 300, 1, 0)          AS\n" +
             "                                           over_three\n" +
             "                                FROM tb_hx) c\n" +
             "                          GROUP BY tickettypeid,\n" +
             "                                   tickettypename) b\n" +
             "                         ON a.tickettypeid = b.tickettypeid\n" +
             "      UNION\n" +
             "      SELECT b.tickettypeid,\n" +
             "             b.tickettypename,\n" +
             "             pd_price,\n" +
             "             pd_count,\n" +
             "             one,\n" +
             "             one_half,\n" +
             "             two,\n" +
             "             two_half,\n" +
             "             three,\n" +
             "             over_three\n" +
             "      FROM (SELECT tickettypeid,\n" +
             "                   tickettypename,\n" +
             "                   SUM(price) AS pd_price,\n" +
             "                   Count(tickettypeid)  AS pd_count\n" +
             "            FROM tb_pd\n" +
             "            GROUP BY tickettypeid,\n" +
             "                     tickettypename) a\n" +
             "               right join (SELECT tickettypeid,\n" +
             "                                  tickettypename,\n" +
             "                                  SUM(one)        AS one,\n" +
             "                                  SUM(one_half)   AS one_half,\n" +
             "                                  SUM(two)        AS two,\n" +
             "                                  SUM(two_half)   AS two_half,\n" +
             "                                  SUM(three)      AS three,\n" +
             "                                  SUM(over_three) AS over_three\n" +
             "                           FROM (SELECT tickettypeid,\n" +
             "                                        tickettypename,\n" +
             "                                        IF(transamount <= 100, 1, 0)         AS one,\n" +
             "                                        IF(transamount > 100\n" +
             "                                               AND transamount <= 150, 1, 0) AS one_half\n" +
             "                                         ,\n" +
             "                                        IF(transamount > 150\n" +
             "                                               AND transamount <= 200, 1, 0) AS two,\n" +
             "                                        IF(transamount > 200\n" +
             "                                               AND transamount <= 250, 1, 0) AS two_half,\n" +
             "                                        IF(transamount > 250\n" +
             "                                               AND transamount <= 300, 1, 0) AS three,\n" +
             "                                        IF(transamount > 300, 1, 0)          AS\n" +
             "                                                                                over_three\n" +
             "                                 FROM tb_hx) c\n" +
             "                           GROUP BY tickettypeid,\n" +
             "                                    tickettypename) b\n" +
             "                          ON a.tickettypeid = b.tickettypeid) e\n" +
             "         left join (SELECT tickettypeid,\n" +
             "                           tickettypename,\n" +
             "                           Count(tickettypeid)         AS rd_count,\n" +
             "                           SUM(refundFee) AS rd_price\n" +
             "                    FROM tb_rd\n" +
             "                    GROUP BY tickettypeid,\n" +
             "                             tickettypename) f\n" +
             "                   ON e.tickettypeid = f.tickettypeid\n" +
             "UNION\n" +
             "SELECT f.tickettypeid,#{dealDate} as date,\n" +
             "       f.tickettypename,\n" +
             "       pd_price,\n" +
             "       pd_count,\n" +
             "       rd_count,\n" +
             "       rd_price,\n" +
             "       one,\n" +
             "       one_half,\n" +
             "       two,\n" +
             "       two_half,\n" +
             "       three,\n" +
             "       over_three\n" +
             "FROM (SELECT a.tickettypeid,\n" +
             "             a.tickettypename,\n" +
             "             pd_price,\n" +
             "             pd_count,\n" +
             "             one,\n" +
             "             one_half,\n" +
             "             two,\n" +
             "             two_half,\n" +
             "             three,\n" +
             "             over_three\n" +
             "      FROM (SELECT tickettypeid,\n" +
             "                   tickettypename,\n" +
             "                   SUM(price) AS pd_price,\n" +
             "                   Count(tickettypeid)  AS pd_count\n" +
             "            FROM tb_pd\n" +
             "            GROUP BY tickettypeid,\n" +
             "                     tickettypename) a\n" +
             "               left join (SELECT tickettypeid,\n" +
             "                                 tickettypename,\n" +
             "                                 SUM(one)        AS one,\n" +
             "                                 SUM(one_half)   AS one_half,\n" +
             "                                 SUM(two)        AS two,\n" +
             "                                 SUM(two_half)   AS two_half,\n" +
             "                                 SUM(three)      AS three,\n" +
             "                                 SUM(over_three) AS over_three\n" +
             "                          FROM (SELECT tickettypeid,\n" +
             "                                       tickettypename,\n" +
             "                                       IF(transamount <= 100, 1, 0)         AS\n" +
             "                                           one,\n" +
             "                                       IF(transamount > 100\n" +
             "                                              AND transamount <= 150, 1, 0) AS\n" +
             "                                           one_half,\n" +
             "                                       IF(transamount > 150\n" +
             "                                              AND transamount <= 200, 1, 0) AS\n" +
             "                                           two,\n" +
             "                                       IF(transamount > 200\n" +
             "                                              AND transamount <= 250, 1, 0) AS\n" +
             "                                           two_half,\n" +
             "                                       IF(transamount > 250\n" +
             "                                              AND transamount <= 300, 1, 0) AS\n" +
             "                                           three,\n" +
             "                                       IF(transamount > 300, 1, 0)          AS\n" +
             "                                           over_three\n" +
             "                                FROM tb_hx) c\n" +
             "                          GROUP BY tickettypeid,\n" +
             "                                   tickettypename) b\n" +
             "                         ON a.tickettypeid = b.tickettypeid\n" +
             "      UNION\n" +
             "      SELECT b.tickettypeid,\n" +
             "             b.tickettypename,\n" +
             "             pd_price,\n" +
             "             pd_count,\n" +
             "             one,\n" +
             "             one_half,\n" +
             "             two,\n" +
             "             two_half,\n" +
             "             three,\n" +
             "             over_three\n" +
             "      FROM (SELECT tickettypeid,\n" +
             "                   tickettypename,\n" +
             "                   SUM(price) AS pd_price,\n" +
             "                   Count(tickettypeid)  AS pd_count\n" +
             "            FROM tb_pd\n" +
             "            GROUP BY tickettypeid,\n" +
             "                     tickettypename) a\n" +
             "               right join (SELECT tickettypeid,\n" +
             "                                  tickettypename,\n" +
             "                                  SUM(one)        AS one,\n" +
             "                                  SUM(one_half)   AS one_half,\n" +
             "                                  SUM(two)        AS two,\n" +
             "                                  SUM(two_half)   AS two_half,\n" +
             "                                  SUM(three)      AS three,\n" +
             "                                  SUM(over_three) AS over_three\n" +
             "                           FROM (SELECT tickettypeid,\n" +
             "                                        tickettypename,\n" +
             "                                        IF(transamount <= 100, 1, 0)         AS one,\n" +
             "                                        IF(transamount > 100\n" +
             "                                               AND transamount <= 150, 1, 0) AS one_half\n" +
             "                                         ,\n" +
             "                                        IF(transamount > 150\n" +
             "                                               AND transamount <= 200, 1, 0) AS two,\n" +
             "                                        IF(transamount > 200\n" +
             "                                               AND transamount <= 250, 1, 0) AS two_half,\n" +
             "                                        IF(transamount > 250\n" +
             "                                               AND transamount <= 300, 1, 0) AS three,\n" +
             "                                        IF(transamount > 300, 1, 0)          AS\n" +
             "                                                                                over_three\n" +
             "                                 FROM tb_hx) c\n" +
             "                           GROUP BY tickettypeid,\n" +
             "                                    tickettypename) b\n" +
             "                          ON a.tickettypeid = b.tickettypeid) e\n" +
             "         right join (SELECT tickettypeid,\n" +
             "                            tickettypename,\n" +
             "                            Count(tickettypeid)         AS rd_count,\n" +
             "                            SUM(refundFee) AS rd_price\n" +
             "                     FROM tb_rd\n" +
             "                     GROUP BY tickettypeid,\n" +
             "                              tickettypename) f\n" +
             "                    ON e.tickettypeid = f.tickettypeid ")
     List<YearCount> selectYearCount(@Param("dealDate") String dealDate);
}
